﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using Model;
/// <summary>
/// ArchiveBorrowRecordService 的摘要说明
/// </summary>
/// 
namespace DA
{
    public class ArchiveBorrowRecordService
    {
        string sql = string.Empty;
        public ArchiveBorrowRecordService()
        {

        }

        //根据借阅ID得到档案借阅信息集合
        public IList<ArchiveBorrowRecord> GetArchiveBorrowRecordByAwardID(string   awardID)
        {
            IList<ArchiveBorrowRecord> archiveBorrowRecords = new List<ArchiveBorrowRecord>();
            sql = "select * from ArchiveBorrowRecord";
            if (awardID.ToString() != "0")
            {
                sql += "where ID=" + awardID.ToString();
            }
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            while (dr.Read())
            {
                ArchiveBorrowRecord archiveBorrowRecord = new ArchiveBorrowRecord();
                archiveBorrowRecord.BorrowerName = Convert.ToString(dr["BorrowerName"]);
                archiveBorrowRecord.BorrowerPhone = Convert.ToString(dr["BorrowerPhone"]);
                archiveBorrowRecord.BorrowPurpose = Convert.ToString(dr["BorrowPurpose"]);
                archiveBorrowRecord.BorrowTime = Convert.ToDateTime(dr["BorrowTime"]);
                archiveBorrowRecord.E_mail = Convert.ToString(dr["E_mail"]);
                archiveBorrowRecord.IfReturn = Convert.ToBoolean(dr["IfReturn"]);
                archiveBorrowRecord.ReturnTime = Convert.ToDateTime(dr["ReturnTime"]);
                archiveBorrowRecord.Remarks = Convert.ToString(dr["Remarks"]);
                archiveBorrowRecords.Add(archiveBorrowRecord);
            }
            return archiveBorrowRecords;
        }

        
        //根据学号得到档案借阅记录
        public IList<ArchiveBorrowRecord> GetArchiveBorrowRecordByStudentID(string studentID)
        {
            IList<ArchiveBorrowRecord> archiveBorrowRecords = new List<ArchiveBorrowRecord>();
            sql = "select * from ArchiveBorrowRecord where StudentID=" +studentID.ToString();
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                ArchiveBorrowRecord archiveBorrowRecord = new ArchiveBorrowRecord();
                archiveBorrowRecord.BorrowID = Convert.ToString(dr["BorrowID"]);
                archiveBorrowRecord.BorrowerName = Convert.ToString(dr["BorrowerName"]);
                archiveBorrowRecord.BorrowerPhone = Convert.ToString(dr["BorrowerPhone"]);
                archiveBorrowRecord.BorrowPurpose = Convert.ToString(dr["BorrowPurpose"]);
                archiveBorrowRecord.BorrowTime = Convert.ToDateTime(dr["BorrowTime"]);
                archiveBorrowRecord.E_mail = Convert.ToString(dr["E_mail"]);
                archiveBorrowRecord.IfReturn = Convert.ToBoolean(dr["IfReturn"]);
                archiveBorrowRecord.ReturnTime = Convert.ToDateTime(dr["ReturnTime"]);
                archiveBorrowRecord.Remarks = Convert.ToString(dr["Remarks"]);
                archiveBorrowRecords.Add(archiveBorrowRecord);
            }
            return archiveBorrowRecords;
        }

        //得到档案借阅记录信息集合
        public IList<ArchiveBorrowRecord> GetArchiveBorrowRecord()
        {
            IList<ArchiveBorrowRecord> archiveBorrowRecords = new List<ArchiveBorrowRecord>();
            sql = "select * from ArchiveBorrowRecord";
            
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            while (dr.Read())
            {
                ArchiveBorrowRecord archiveBorrowRecord = new ArchiveBorrowRecord();
                archiveBorrowRecord.BorrowID = Convert.ToString(dr["BorrowID"]);
                archiveBorrowRecord.StudentID = Convert.ToString(dr["StudentID"]);
                archiveBorrowRecord.BorrowerName = Convert.ToString(dr["BorrowerName"]);
                archiveBorrowRecord.BorrowerPhone = Convert.ToString(dr["BorrowerPhone"]);
                archiveBorrowRecord.BorrowPurpose = Convert.ToString(dr["BorrowPurpose"]);
                archiveBorrowRecord.BorrowTime = Convert.ToDateTime(dr["BorrowTime"]);
                archiveBorrowRecord.E_mail = Convert.ToString(dr["E_mail"]);
                archiveBorrowRecord.IfReturn = Convert.ToBoolean(dr["IfReturn"]);
                archiveBorrowRecord.ReturnTime = Convert.ToDateTime(dr["ReturnTime"]);
                archiveBorrowRecord.Remarks = Convert.ToString(dr["Remarks"]);
                archiveBorrowRecords.Add(archiveBorrowRecord);
            }
            return archiveBorrowRecords;
        }

        //根据ID得到档案借阅记录实体对象
        public ArchiveBorrowRecord GetArchiveBorrowRecordByID(string  id)
        {
            ArchiveBorrowRecord archiveBorrowRecord = new ArchiveBorrowRecord();
            sql = "select * from ArchiveBorrowRecord where BorrowID=" + id.ToString();
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            while (dr.Read())
            {
                archiveBorrowRecord.StudentID = Convert.ToString(dr["StudentID"]);
                archiveBorrowRecord.BorrowerName = Convert.ToString(dr["BorrowerName"]);
                archiveBorrowRecord.BorrowerPhone = Convert.ToString(dr["BorrowerPhone"]);
                archiveBorrowRecord.BorrowPurpose = Convert.ToString(dr["BorrowPurpose"]);
                archiveBorrowRecord.BorrowTime = Convert.ToDateTime(dr["BorrowTime"]);
                archiveBorrowRecord.E_mail = Convert.ToString(dr["E_mail"]);
                archiveBorrowRecord.IfReturn = Convert.ToBoolean(dr["IfReturn"]);
                archiveBorrowRecord.ReturnTime = Convert.ToDateTime(dr["ReturnTime"]);
                archiveBorrowRecord.Remarks = Convert.ToString(dr["Remarks"]);
               
            }
            return archiveBorrowRecord;
        }

        //删除档案借阅记录信息
        public void DeleteArchiveBorrowRecordByID(string  id)
        {
            sql = "delete from ArchiveBorrowRecord where BorrowID=" + Convert.ToString(id);
            DB.DBHelper.ExecuteCommand(sql);
        }

        //修改档案借阅记录信息
        public int  ModifyArchiveBorrowRecord(ArchiveBorrowRecord archiveBorrowRecord)
        {
            int id;
            StringBuilder updateArchiveBorrowRecord = new StringBuilder();
            updateArchiveBorrowRecord.Append("update ArchiveBorrowRecord set BorrowerName='" + archiveBorrowRecord.BorrowerName + "',BorrowerPhone='" + archiveBorrowRecord.BorrowerPhone
                + "',BorrowPurpose='" + archiveBorrowRecord.BorrowPurpose + "',BorrowTime='" + archiveBorrowRecord.BorrowTime + "',E_mail='" + archiveBorrowRecord.E_mail
                + "',IfReturn='" + archiveBorrowRecord.IfReturn + "',ReturnTime='" + archiveBorrowRecord.ReturnTime + "',Remarks='" + archiveBorrowRecord.Remarks + "'where BorrowID=" + archiveBorrowRecord.BorrowID);
            sql = updateArchiveBorrowRecord.ToString();
            id=DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

        //增加档案借阅信息
        public int AddArchiveBorrowRecord(ArchiveBorrowRecord archiveBorrowRecord)
        {
            int id;
            StringBuilder addArchiveBorrowRecord = new StringBuilder();
            addArchiveBorrowRecord.Append("insert into ArchiveBorrowRecord(BorrowID,StudentID,BorrowerName,BorrowerPhone,BorrowPurpose,BorrowTime,E_mail,IfReturn,ReturnTime,Remarks) values('"+archiveBorrowRecord.BorrowID+"','"
                +archiveBorrowRecord.StudentID+"','"+ archiveBorrowRecord.BorrowerName + "','" + archiveBorrowRecord.BorrowerPhone
                + "','" + archiveBorrowRecord.BorrowPurpose + "','" + archiveBorrowRecord.BorrowTime + "','" + archiveBorrowRecord.E_mail + "','" 
                + archiveBorrowRecord.IfReturn + "','" + archiveBorrowRecord.ReturnTime + "','" + archiveBorrowRecord.Remarks + "') select @@IDENTITY");
            sql = addArchiveBorrowRecord.ToString();
            id = DB.DBHelper.ExecuteCommand(sql);
            return id;
        }
    }
}